alter table AP
add ClientId varchar(50) null
go

select *
into AP_tmp
from AP
go

drop table AP
go

CREATE TABLE [dbo].[AP](
	[APId] [int] IDENTITY(1,1) NOT NULL,
	[ClientId] [varchar](50) NULL,
	[RegionName] [varchar](100) NOT NULL,
	[CityName] [varchar](100) NOT NULL,
	[SalePointName] [varchar](500) NOT NULL,
	[SalePointJurName] [varchar](500) NULL,
	[Address] [varchar](1000) NULL,
	[SaleChannelId] [int] NULL,
	[StorePointId] [int] NULL,
	[Depart1UserId] [int] NULL,
	[Depart2UserId] [int] NULL,
	[Depart3UserId] [int] NULL,
	[Depart4UserId] [int] NULL,
	[Depart5UserId] [int] NULL,
	[IsDelete] [bit] NOT NULL
 CONSTRAINT [PK_AP] PRIMARY KEY CLUSTERED 
(
	[APId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AP]  WITH CHECK ADD  CONSTRAINT [FK_AP_SaleChannel] FOREIGN KEY([SaleChannelId])
REFERENCES [dbo].[SaleChannel] ([SaleChannelId])
GO
ALTER TABLE [dbo].[AP] CHECK CONSTRAINT [FK_AP_SaleChannel]
GO
ALTER TABLE [dbo].[AP]  WITH CHECK ADD  CONSTRAINT [FK_AP_StorePoint] FOREIGN KEY([StorePointId])
REFERENCES [dbo].[StorePoint] ([StorePointId])
GO
ALTER TABLE [dbo].[AP] CHECK CONSTRAINT [FK_AP_StorePoint]
GO
ALTER TABLE [dbo].[AP]  WITH CHECK ADD  CONSTRAINT [FK_AP_User] FOREIGN KEY([Depart1UserId])
REFERENCES [dbo].[User] ([Id])
GO
ALTER TABLE [dbo].[AP] CHECK CONSTRAINT [FK_AP_User]
GO
ALTER TABLE [dbo].[AP]  WITH CHECK ADD  CONSTRAINT [FK_AP_User1] FOREIGN KEY([Depart2UserId])
REFERENCES [dbo].[User] ([Id])
GO
ALTER TABLE [dbo].[AP] CHECK CONSTRAINT [FK_AP_User1]
GO
ALTER TABLE [dbo].[AP]  WITH CHECK ADD  CONSTRAINT [FK_AP_User2] FOREIGN KEY([Depart3UserId])
REFERENCES [dbo].[User] ([Id])
GO
ALTER TABLE [dbo].[AP] CHECK CONSTRAINT [FK_AP_User2]
GO
ALTER TABLE [dbo].[AP]  WITH CHECK ADD  CONSTRAINT [FK_AP_User3] FOREIGN KEY([Depart4UserId])
REFERENCES [dbo].[User] ([Id])
GO
ALTER TABLE [dbo].[AP] CHECK CONSTRAINT [FK_AP_User3]
GO
ALTER TABLE [dbo].[AP]  WITH CHECK ADD  CONSTRAINT [FK_AP_User4] FOREIGN KEY([Depart5UserId])
REFERENCES [dbo].[User] ([Id])
GO
ALTER TABLE [dbo].[AP] CHECK CONSTRAINT [FK_AP_User4]
GO
ALTER TABLE [dbo].[AP] ADD  CONSTRAINT [DF_AP_IsDelete]  DEFAULT ((0)) FOR [IsDelete]
GO

declare @ColumnName varchar(100)
declare @ColumnList varchar(8000) = ''
declare @Sql varchar(8000) = ''

declare curs cursor for 
select COLUMN_NAME
from Analysis.information_schema.columns
where table_name = 'AP_tmp' and COLUMN_NAME like 'Custom %'
order by table_name, ordinal_position

open curs
fetch from curs into @ColumnName
while @@FETCH_STATUS = 0
begin
	execute ('alter table AP add [' + @ColumnName + '] varchar(100) null')
	set @ColumnList = @ColumnList + '[' + @ColumnName + '],'
	fetch from curs into @ColumnName
end
close curs
deallocate curs

if (@ColumnList <> '')
	set @ColumnList = SUBSTRING(@ColumnList, 1, len(@ColumnList) - 1)
--select @ColumnList	

set @Sql = 'insert into AP 
			(ClientId, RegionName, CityName, SalePointName, SalePointJurName, Address, SaleChannelId, StorePointId, 
			Depart1UserId, Depart2UserId, Depart3UserId, Depart4UserId, Depart5UserId, IsDelete'
if @ColumnList <> ''
	set @Sql = @Sql + ', ' + @ColumnList
set @Sql = @Sql + ') select ClientId, RegionName, CityName, SalePointName, SalePointJurName, Address, SaleChannelId, StorePointId, 
			Depart1UserId, Depart2UserId, Depart3UserId, Depart4UserId, Depart5UserId, IsDelete'	
if @ColumnList <> ''
	set @Sql = @Sql + ', ' + @ColumnList			
set @Sql = @Sql + ' from AP_tmp order by APId '	
execute (@Sql)
--select @Sql
go

drop table
AP_tmp
go

